set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
with tmp_view as (
    select waybill_no
         , resp_network
         , resp_net
         , network_cnt
         , round(1 / network_cnt, 4) as waybill_no_num
         , date_time
         , work_type
         , case
               when resp_network = resp_network_code then goods_value
               when resp_network = resp_network_code_2 then goods_value2
               when resp_network = resp_network_code_3 then goods_value3
               when resp_network = resp_network_code_4 then goods_value4
               when resp_network = resp_network_code_5 then goods_value5
               else 0 end            as goods_value
         , is_delete
         , damage_eliminate
         , overtime_flag
         , warn_worn_flag
         , high_worn_flag
         , pick_network_code
         , pick_network_name
         , pick_agent_code
         , pick_agent_name
         , pick_network_type
         --新增
         , computer_flag
    from (
             select waybill_no
                  , date_time
                  , array(resp_network_code,
                          resp_network_code_2,
                          resp_network_code_3,
                          resp_network_code_4,
                          resp_network_code_5) as resp_net
                  , work_type
                  , network_cnt
                  , resp_network_code
                  , resp_network_code_2
                  , resp_network_code_3
                  , resp_network_code_4
                  , resp_network_code_5
                  , goods_value
                  , goods_value2
                  , goods_value3
                  , goods_value4
                  , goods_value5
                  , is_delete
                  , damage_eliminate
                  , overtime_flag
                  , warn_worn_flag
                  , high_worn_flag
                  , pick_network_code
                  , pick_network_name
                  , pick_agent_code
                  , pick_agent_name
                  , pick_network_type
                  --新增
                  , computer_flag
             from bidefault.dm_sqs_worn_waybill_detail_mid_dt
             where dt >= date_add('{{ execution_date | cst_ds }}', -89)
               and dt <= '{{ execution_date | cst_ds }}'
               and resp_network_code is not null
         ) tmp
             lateral view explode(resp_net) resp_net_detail as resp_network
    where resp_network is not null
      and resp_network <> ''
),
     network_massage as (
         select code          --编号
              , name          --名称
              , network_type  --网点类型
              , virt_code     --虚拟代理区code
              , virt_name     --虚拟代理区name
              , manage_code   --管理大区名称
              , manage_name   --管理大区名称
              , regional_id   --大区ID
              , regional_desc --regional_desc
              , agent_code    --代理区编码
              , agent_name    --代理区名称
              , fran_code     --加盟商编码
              , fran_name     --加盟商名称
              , center_code   --中心编码
              , center_name   --中心名称
              , provider_id   --省份ID
              , provider_desc --省份
              , city_id       --城市ID
              , city_desc     --城市
              , area_id       --区/县id
              , area_desc     --区/县
              , is_enable
              , is_suspend
              , id as resp_network_id
              , zone_id
              , zone_code
              , zone_name
              , financial_center_code
              , financial_center_desc
              , parent_area_code
              , parent_area_name
         from jms_dim.dim_network_whole_massage
         where (name = '承运商'
             or (agent_name not like '%BEST%'
                 and agent_name not like '%总部%'
                 and agent_name not like '%测试%')
                   )
     )

insert
overwrite
table
bidefault.dm_sqs_worn_waybill_detail_dt
partition
(
dt
)
select t1.waybill_no                                                as waybill_no         --运单
     , t1.work_type                                                 as work_type          --工单类型
     , t2.resp_network_id                                           as resp_network_id    --责任网点id
     , t1.resp_network                                              as resp_network_code  --责任网点code
     , t2.name                                                      as resp_network_name  --责任网点名字
     , t2.network_type                                              as duty_network_type  --网点类型
     , t2.virt_code                                                 as duty_virt_code     --虚拟代理区code
     , t2.virt_name                                                 as duty_virt_name     --虚拟代理区name
     , t2.manage_code                                               as duty_manage_code   --管理大区名称
     , t2.manage_name                                               as duty_manage_name   --管理大区名称
     , t2.regional_id                                               as duty_regional_id   --大区ID
     , t2.regional_desc                                             as duty_regional_desc --大区名
     , if(t2.name = '承运商', t2.financial_center_code, t2.agent_code) as duty_agent_code    --代理区编码
     , if(t2.name = '承运商', t2.financial_center_desc, t2.agent_name) as duty_agent_name    --代理区名称
     , t2.fran_code                                                 as duty_fran_code     --加盟商编码`
     , t2.fran_name                                                 as duty_fran_name     --加盟商名称
     , t2.center_code                                               as duty_center_code   --中心编码
     , t2.center_name                                               as duty_center_name   --中心名称
     , t2.provider_id                                               as duty_provider_id   --省份ID
     , t2.provider_desc                                             as duty_provider_desc --省份
     , t2.city_id                                                   as duty_city_id       --城市ID
     , t2.city_desc                                                 as duty_city_desc     --城市
     , t2.area_id                                                   as duty_area_id       --区/县id
     , t2.area_desc                                                 as duty_area_desc     --区/县
     , t2.is_enable                                                 as is_enable          --是否可用
     , t2.is_suspend                                                as is_suspend         --是否暂停(1是2否)默认2-否
     , t1.resp_net                                                  as resp_net           -- 拼接责任网点
     , t1.network_cnt                                               as network_cnt        --责任网点个数
     , t1.waybill_no_num                                            as waybill_no_num     --有效破顺票数
     , t1.goods_value                                               as goods_value        --物品金额
     , t1.date_time                                                 as date_time          --日期
     , t2.zone_id                                                   as zone_id            --片区
     , t2.zone_code                                                 as zone_code          --片区
     , t2.zone_name                                                 as zone_name          --片区
     , t1.is_delete                                                 as is_delete          --是否重复
     , t1.damage_eliminate                                          as damage_eliminate   --是否
     , t1.overtime_flag
     , t1.warn_worn_flag
     , t1.high_worn_flag
     , t1.pick_network_code
     , t1.pick_network_type
     , t1.pick_agent_code
     , t1.pick_network_name
     , t1.pick_agent_name
     --新增
     , t1.computer_flag
     , t2.parent_area_code                                          as duty_parent_area_code
     , t2.parent_area_name                                          as duty_parent_area_name
     , t1.date_time                                                 as dt
from tmp_view t1
         left join network_massage t2
                   on t1.resp_network = t2.code
         left join network_massage t3
                   on t1.pick_network_code = t3.code
    distribute by dt
;
